﻿using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Data.Common;

namespace CommonUtils
{
    /// <summary>
    /// ADO.NET for MySql
    /// </summary>
    public class DbMySql : DbBase
    {
        public static class ConnectStrings
        {
            public static string Get(string host, string user, string pwd, string database, int port = 3306)
            => string.Format("server={0};port={1};user={2};pwd={3};database={4};sslmode=none;", host, port, user, pwd, database);

            public static string GetDefault(string database = "test")
            {
                var connectString = Get("127.0.0.1", "root", "123456", database);
                LogUtil.PrintInfo(connectString);
                return connectString;
            }
        }

        public DbMySql(string connectString)
        : base(connectString) { }

        public DbMySql(string host, string user, string pwd, string database, int port = 3306)
        : this(ConnectStrings.Get(host, user, pwd, database, port)) { }

        protected override DbConnection GetConnection()
        => new MySqlConnection(ConnectString);

        protected override DbParameter GetParameter(string key, object value)
        => new MySqlParameter(key, value);

        [Obsolete]
        protected override DbDataAdapter GetDataAdapter(DbCommand cmd)
        => new MySqlDataAdapter((MySqlCommand)cmd);

        public override void BulkInsert(DataTable table)
        {
            if (table.TableName.IsNullOrEmpty())
                throw new ExceptionPlus("Empty TableName Not Allowed!");
            var path = CsvUtil.CreateTempFile(table);
            using (var connection = (MySqlConnection)GetConnection())
            {
                var bulk = new MySqlBulkLoader(connection)
                {
                    FileName = path,
                    TableName = table.TableName
                };
                bulk.Load();
            }
            FileUtil.Delete(path);
        }

        protected override char FieldStartIdentifier { get; } = '`';

        protected override char FieldEndIdentifier { get; } = '`';

        public void ShowLogState()
        {
            ConsoleUtil.WriteLine("Log开启状态:");
            GetDataTable("show variables like 'general_log';").PrintJson();
            ConsoleUtil.WriteLine("Log输出目标:");
            GetDataTable("show variables like 'log_output';").PrintJson();
            ConsoleUtil.WriteLine("Log文件位置:");
            GetDataTable("show variables like 'general_log_file';").PrintJson();
        }

        //重启后还原，长期修改需编辑 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

        /// <summary>
        /// 设置log路径 path：???.log.sql
        /// </summary>
        public void SetLogFilePath(string path)
        {
            ConsoleUtil.WriteLine("SetLogFilePath:");
            Execute("set global general_log_file=@general_log_file;", new StringObjectMap("general_log_file", path));
        }

        public void SwitchLogOutputToTable()
        {
            ConsoleUtil.WriteLine("SwitchLogOutputToTable:");
            Execute("set global log_output = 'table';");
        }

        public void SwitchLogOutputToFile()
        {
            ConsoleUtil.WriteLine("SwitchLogOutputToFile:");
            Execute("set global log_output='file';");
        }

        public void OpenLog()
        {
            ConsoleUtil.WriteLine("OpenLog:");
            Execute("set global general_log = 'ON';");
        }

        public void OpenTableLog()
        {
            ConsoleUtil.WriteLine("OpenDbLog:");
            SwitchLogOutputToTable();
            OpenLog();
        }

        /// <summary>
        /// 开启文件日志 path：???.log.sql
        /// </summary>
        public void OpenFileLog(string path)
        {
            ConsoleUtil.WriteLine("OpenFileLog:");
            SetLogFilePath(path);
            SwitchLogOutputToFile();
            OpenLog();
        }

        public void CloseLog()
        {
            ConsoleUtil.WriteLine("CloseLog:");
            Execute("set global general_log = 'OFF';");
        }

        public void ShowLogTimeZone()
        {
            ConsoleUtil.WriteLine("ShowLogTimeZone:");
            GetDataTable("show variables like 'log_timestamps';").PrintJson();
        }

        /// <summary>
        /// 默认UTC时间 0时区
        /// </summary>
        public void SetLogTimeZone()
        {
            ConsoleUtil.WriteLine("SetLogTimeZone:");
            //my.ini [mysqld] log_timestamps = SYSTEM
            Execute("set global log_timestamps = 'SYSTEM';");
        }

        public void ShowTimeZone()
        {
            ConsoleUtil.WriteLine("ShowTimeZone:");
            GetDataTable("show variables like 'time_zone';").PrintJson();
        }

        /// <summary>
        /// 这个一般不用改 默认系统时间
        /// </summary>
        public void SetTimeZone()
        {
            ConsoleUtil.WriteLine("SetTimeZone:");
            //my.ini [mysqld] default-time_zone = '+8:00'
            Execute("set global time_zone = '+8:00';");
        }

        /// <summary>
        /// 本地默认配置的数据库
        /// </summary>
        public static DbMySql GetLocalDefault(string database = "test")
        => new DbMySql(ConnectStrings.GetDefault(database));
    }
}
